package com.trevor.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;



public class SqlUtil {
	
		private Connection ct=null;
		private PreparedStatement pre=null;
		private ResultSet rs=null;
		private static String userName="";
		private static String password="";
		private static String driver="";
		private static String url="";
		private static InputStream is=null;
		static{
			Properties pp=new Properties();
			try{
				is=SqlUtil.class.getClassLoader().getResourceAsStream("mysql.properties");
				pp.load(is);
				userName=pp.getProperty("dbUserName");
				password=pp.getProperty("dbPassword");
				driver=pp.getProperty("dbDriver");
				url=pp.getProperty("dbUrl");
				Class.forName(driver);
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				try {
					is.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		public Connection getConnection(){
			try {
				ct=DriverManager.getConnection(url, userName, password);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return ct;
		}
		public ArrayList executeQuery(String sql,String[] paras){
			ct=getConnection();
			ArrayList al=new ArrayList();
			try{
				pre=ct.prepareStatement(sql);
				if(paras != null){
					for(int i=0;i<paras.length;i++){
						pre.setString(i+1, paras[i]);
					}
				}
				rs=pre.executeQuery();
				ResultSetMetaData rsmd=rs.getMetaData();
				int column=rsmd.getColumnCount();
				while(rs.next()){
					
					Object[] obj=new Object[column];
					for(int i=1;i<=column;i++){
						obj[i-1]=rs.getObject(i);
					}
					al.add(obj);
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
				throw new RuntimeException("excutesqlresultset  error!"+e.getMessage());
			}finally{
				try{
					rs.close();
					pre.close();
					ct.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			return al;
		}
		
		public boolean insertItem(String sql,String[] paras){
			ct=getConnection();
			try{
				pre=ct.prepareStatement(sql);
				if(paras != null){
					for(int i=0;i<paras.length;i++){
						pre.setString(i+1, paras[i]);
					}
				}
				return pre.execute();
			}catch(Exception e){
				e.printStackTrace();
				return false;
			}
		}
}
